﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using System.Collections;

namespace DocumentManager
{
    public partial class UsersDocuments : Form
    {
        private BindingSource bindingSource1 = new BindingSource();

        public UsersDocuments()
        {
            InitializeComponent();
        }

        private void UsersDocuments_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'trainingCertificationsDataSet1.Users' table. You can move, or remove it, as needed.
            //this.usersTableAdapter.Fill(this.trainingCertificationsDataSet1.Users);
            ArrayList users = CommonFunctions.GetAllUsersFromDB();
            cbUsers.DisplayMember = "Text";
            cbUsers.ValueMember = "Value";
            cbUsers.DataSource = users;

            gvDocuments.DataSource = bindingSource1;
            GetData("Select ID, Man_Number as [Man Number], Created_Date as [Created Date], Description as [Description], Course_Date as [Course Date],Document from Documents");
        }

        private void GetData(String selectCommand)
        {
            try
            {
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(selectCommand, ((Form1)MdiParent).ConnectionString);
                OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
                gvDocuments.AutoGenerateColumns = false;
                DataTable table = new DataTable();
                dataAdapter.Fill(table);
                bindingSource1.DataSource = table;
                gvDocuments.Columns.Clear();

                DataGridViewColumn cDescription = new DataGridViewTextBoxColumn();
                cDescription.DataPropertyName = "Description";
                cDescription.Name = "Description";
                gvDocuments.Columns.Add(cDescription);

                DataGridViewColumn cManNumber = new DataGridViewTextBoxColumn();
                cManNumber.DataPropertyName = "Man Number";
                cManNumber.Name = "Man Number";
                gvDocuments.Columns.Add(cManNumber);

                DataGridViewColumn cCreatedDate = new DataGridViewTextBoxColumn();
                cCreatedDate.DataPropertyName = "Created Date";
                cCreatedDate.Name = "Created Date";
                gvDocuments.Columns.Add(cCreatedDate);

                DataGridViewColumn cCourseDate = new DataGridViewTextBoxColumn();
                cCourseDate.DataPropertyName = "Course Date";
                cCourseDate.Name = "Course Date";
                gvDocuments.Columns.Add(cCourseDate);

                //Add an image with a click reference
                DataGridViewButtonColumn column = new DataGridViewButtonColumn();
                //column.DataPropertyName = "View";
                column.Name = "Document";
                column.HeaderText = "Document";
                column.UseColumnTextForButtonValue = true;
                column.Text = "View";
                column.Width = 65;
                
                gvDocuments.Columns.Add(column);

                DataGridViewColumn cID = new DataGridViewTextBoxColumn();
                cID.DataPropertyName = "ID";
                cID.Name = "ID";
                cID.Visible = false;
                gvDocuments.Columns.Add(cID);

            }
            catch (OleDbException oex)
            {
                MessageBox.Show("Filling GV Crashed.");
                Console.WriteLine("Msg:" + oex.Message);
                Console.WriteLine("Exc:" + oex.StackTrace.ToString());
            }
        }

        private void UsersDocuments_Enter(object sender, EventArgs e)
        {
            gvDocuments.DataSource = bindingSource1;
            GetData("Select ID, Man_Number as [Man Number], Created_Date as [Created Date], Description as [Description], Course_Date as [Course Date],Document from Documents");
        }

        private void gvDocuments_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if ( e.ColumnIndex.Equals(4))
            {
                String rootPath = ConfigurationManager.AppSettings["TargetTempDir"].ToString();

                //verify path exists, if not create it
                if (!System.IO.Directory.Exists(rootPath))
                {
                    try
                    {
                        System.IO.Directory.CreateDirectory(rootPath);
                    }
                    catch { Console.WriteLine("Couldn't create the directory."); }
                }

                //rootPath = "./";
                Console.WriteLine(":" + gvDocuments.CurrentRow.Cells[5].Value);
                //render 
                String sql = "Select Document, Man_Number, Description, Course_Date from Documents where ID = @id";
                //OleDbDataAdapter dataAdapter = new OleDbDataAdapter(selectCommand, ((Form1)MdiParent).ConnectionString);
                OleDbConnection con = new OleDbConnection(((Form1)MdiParent).ConnectionString);
                OleDbCommand command = con.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddWithValue("@id", gvDocuments.CurrentRow.Cells[5].Value);

                try
                {
                    con.Open();
                    FileStream fs; 
                    BinaryWriter bw;
                    int startIndex = 0;
                    int bufferSize = 100;                   // Size of the BLOB buffer.
                    byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
                    long retval;                            // The bytes returned from GetBytes.

                    OleDbDataReader dataReader = command.ExecuteReader();
                    byte[] Blob = null;
                    if (dataReader.HasRows)
                    {
                        dataReader.Read();

                        //Blob = new Byte[(dataReader.GetBytes(0, 0, Blob, 0, int.MaxValue))];
                        //retval = dataReader.GetBytes(0, 0, Blob, 0, Blob.Length);

                        //Write the blob out
                        DateTime courseDate = (DateTime)dataReader[3];
                        String shortDate = courseDate.ToShortDateString().Replace("/","");
                        String fileName = dataReader[1] + "_" + dataReader[2] + "_" + shortDate + ".pdf";
                        fs = new FileStream(rootPath + fileName, FileMode.OpenOrCreate, FileAccess.Write);
                        bw = new BinaryWriter(fs);
                        //// Read the bytes into outbyte[] and retain the number of bytes returned.
                        retval = dataReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);

                        //// Continue reading and writing while there are bytes beyond the size of the buffer.
                        while (retval == bufferSize)
                        {
                            bw.Write(outbyte);
                            bw.Flush();

                            // Reposition the start index to the end of the last buffer and fill the buffer.
                            startIndex += bufferSize;
                            retval = dataReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);
                        }
                        
                        // Write the remaining buffer.
                        //bw.Write(Blob, 0, (int)retval);
                        
                        bw.Write(outbyte, 0, (int)retval);
                        bw.Flush();

                        // Close the output file.
                        bw.Close();
                        fs.Close();

                        //now open adobe and the file
                        //
                        //System.Diagnostics.Process.Start();
                        System.Diagnostics.Process.Start(rootPath + fileName);


                    }
                    dataReader.Close();


 
                }
                catch (OleDbException ode)
                {

                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                }
                
            }
        }

        private void cbUsers_SelectedIndexChanged(object sender, EventArgs e)
        {
            //this.usersTableAdapter.Fill(this.trainingCertificationsDataSet1.Users);
            gvDocuments.DataSource = bindingSource1;
            Console.WriteLine("Man_NUmber:" + cbUsers.SelectedValue);
            if (cbUsers.SelectedIndex == 0 || cbUsers.SelectedIndex == -1)
            {
                GetData("Select ID, Man_Number as [Man Number], Created_Date as [Created Date], Description as [Description], Course_Date as [Course Date],Document from Documents");
            }
            else
            {
                GetData("Select ID, Man_Number as [Man Number], Created_Date as [Created Date], Description as [Description], Course_Date as [Course Date],Document from Documents where Man_Number=" + cbUsers.SelectedValue + "");
            }
        }
    }
}
